﻿<cfsilent>
	<cfscript>
		
		sql = "SELECT 
					institute_id, institute_name 
				FROM 
					t_institute 
				WHERE 
					institute_id = :instituteId ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="instituteId", value=event.getArg("DepID"), cfsqltype="cf_sql_varchar" );
		rs_departmentEntity = queryObj.execute( sql=sql ).getResult(); 
		
		termID = event.getArg("TermID");
		academicYear = event.getArg("AcademicYear");
		academicTerm = event.getArg("AcademicTerm");
		
		sql = "SELECT 
					t_course.course_name, 
					t_task.cidx, 
					t_teacher.tch_name, 
					t_teacher.tch_id, 
					t_task.task_week, 
					t_task.task_period, 
					t_task.task_limite, 
					t_task.task_classes, 
					t_campus.campus_name, 
					t_classroom_type.cr_type_name, 
					CASE WHEN t_task.task_desc IS NULL THEN '-' ELSE t_task.task_desc END task_desc, 
					t_course.course_code,
					t_course.period_theory, 
					t_course.period_practice, 
					t_course.period_computer 
				FROM 
					t_task 
					INNER JOIN t_course ON t_course.cid = t_task.cid 
					INNER JOIN t_teacher ON t_teacher.tch_id = t_task.tch_id 
					INNER JOIN t_campus ON t_campus.campus_id = t_task.campus_id 
					INNER JOIN t_classroom_type ON t_classroom_type.cr_type = t_task.cr_type 
				WHERE 
					t_task.term_id = :term 
					AND 
					t_course.institute_id = :depID 
					AND 
					SUBSTR(t_task.task_prop, 7, 1) = '0' 
				ORDER BY 
					t_course.course_name, 
					t_task.cidx ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="term", value=termID, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="depID", value=rs_departmentEntity.institute_id, cfsqltype="cf_sql_varchar" );
		rs_task = queryObj.execute( sql=sql ).getResult(); 
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_departmentEntity.institute_name & "合班教学任务", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("计划课程", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "教学班,课程,课序号,课程号,理论学时,实践学时,上机学时,教师,职工号,周次,周学时,计划人数,上课班级,上课校区,教室类别,排课要求");
		
		for ( row=1; row LTE rs_task.recordCount; row++ ) {
			
			/* 构建教学班信息 */
			rowData = rs_task["course_name"][row]&"-"&rs_task["tch_name"][row]&"["&rs_task["cidx"][row]&"]" 
						& ","
						& rs_task["course_name"][row]
						& ","
						& rs_task["cidx"][row]
						& ","
						& rs_task["course_code"][row]
						& ","
						& rs_task["period_theory"][row]
						& ","
						& rs_task["period_practice"][row]
						& ","
						& rs_task["period_computer"][row]
						& ","
						& rs_task["tch_name"][row]
						& ","
						& rs_task["tch_id"][row]
						& ","
						& rs_task["task_week"][row]
						& ","
						& rs_task["task_period"][row]
						& ","
						& rs_task["task_limite"][row]
						& ","
						& replace(rs_task["task_classes"][row], ",", "，", "all")
						& ","
						& rs_task["campus_name"][row]
						& ","
						& rs_task["cr_type_name"][row]
						& ","
						& replace(rs_task["task_desc"][row], ",", "，", "all");
			
			/* 写入教学班信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>